package org.agle4j.framework.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	private InputStream is;
	private OutputStream os;
	private int sheetNum = 0;
	private String ext ;

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	/**
	 * @param ext : xls or xlsx
	 */
	public ExcelUtil(InputStream is, String ext) {
		this.is = is;
		this.ext = ext;
	}
	
	/**
	 * @param ext : xls or xlsx
	 */
	public ExcelUtil(InputStream is) {
		this.is = is;
	}

	/**
	 * @param ext : xls or xlsx
	 */
	public ExcelUtil(OutputStream os, String ext) {
		this.os = os;
		this.ext = ext;
	}
	
	/**
	 * 使用此方法必须构造 ExcelUtil(InputStream is, String ext) 方法
	 */
	public Iterator<Row> read() throws IOException {
		if (StringUtils.equals("xls", ext)) {
			return readXls();
		} else if (StringUtils.equals("xlsx", ext)) {
			return readXlsx();
		}
		return null;
	}

	/**
	 * 使用此方法须构造 ExcelUtil(InputStream is) 方法
	 */
	public Iterator<Row> readXls() throws IOException {
		Workbook wb = new HSSFWorkbook(is);
		return read(wb);
	}
	
	/**
	 * 使用此方法须构造 ExcelUtil(InputStream is) 方法
	 */
	public Iterator<Row> readXlsx() throws IOException {
		Workbook wb = new XSSFWorkbook(is);
		return read(wb);
	}

	private Iterator<Row> read(Workbook wb) {
		Sheet sheet = wb.getSheetAt(sheetNum);
		return sheet.iterator();
	}

	/**
	 * 使用此方法必须构造ExcelTools(OutputStream os, String ext)方法
	 */
	public void write(List<Map<String, Object>> dataList, String sheetName) throws Exception {
		if (CollectionUtils.isEmpty(dataList)||StringUtils.isEmpty(sheetName)) {
			return ;
		}
		Workbook wb = null;
		try {
			if (StringUtils.equals("xls", ext)) {
				wb = new HSSFWorkbook();
			} else if (StringUtils.equals("xlsx", ext)) {
				wb = new XSSFWorkbook();
			}
			Sheet sheet = wb.createSheet(sheetName);

			// 设置列
			Row row0 = sheet.createRow(0);
			Map<String, Object> titleMap = dataList.get(0);
			List<String> titleList = new ArrayList<>(titleMap.keySet());
			for (int i = 0; i < titleList.size(); i++) {
				Cell cell = row0.createCell(i, Cell.CELL_TYPE_STRING);
				cell.setCellValue(String.valueOf(titleList.get(i)));
			}

			// 设置值
			for (int rowNum = 0; rowNum < dataList.size() ; rowNum++) {
				Row row = sheet.createRow(rowNum+1);//因为第一行是列, 向下移一行
				Map<String, Object> rowMap = dataList.get(rowNum);
				List<Object> cellList = new ArrayList<>(rowMap.values());
				for (int cellNum = 0; cellNum < cellList.size(); cellNum++) {
					Cell cell = row.createCell(cellNum, Cell.CELL_TYPE_STRING);
					cell.setCellValue(String.valueOf(cellList.get(cellNum)));
				}
			}
			wb.write(os);
			os.flush();
		} catch (Exception e) {
			throw e;
		} finally {
			wb.close();
			IOUtils.closeQuietly(os);
		}
	}

	public static String getCellValue(Cell cell) {
		Object result = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				result = cell.getNumericCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_FORMULA:
				result = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_ERROR:
				result = cell.getErrorCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				break;
			default:
				break;
			}
		}
		return result.toString();
	}

	public static void main(String[] args) throws Exception {
		if (true) { // write
			OutputStream os = new FileOutputStream(new File("e:\\test1.xlsx")) ;
			ExcelUtil et = new ExcelUtil(os,"xlsx");
			List<Map<String, Object>> dataList = new ArrayList<>();
			for (int i = 0; i < 3; i++) {
				Map<String, Object> map = new HashMap<>();
				map.put("name", "hanyx" + i);
				map.put("age", "30" + i);
				map.put("sex", "men" + i);
				dataList.add(map);
			}
			et.write(dataList, "test");
		} else { // read
			String writeFile = "e:\\test1.xlsx";
			InputStream is = new FileInputStream(new File(writeFile)) ;
			ExcelUtil et = new ExcelUtil(is,"xlsx");
			Iterator<Row> ite = et.read();
			while (ite.hasNext()) {
				Row row = ite.next();
				if (row != null) {
					// 获取每一列值
					for (int x = 0; x < row.getLastCellNum(); x++) {
						Cell cell = row.getCell(x);
						String value = ExcelUtil.getCellValue(cell);
						if (!value.equals("")) {
							System.out.print(value + "|");
						}
					}
				}
				System.out.println();
			}
		}
	}

}
